package org.streets.eis.ext.analysis.internal.impl;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.streets.commons.util.StringUtils;
import org.streets.eis.ext.analysis.entities.QueryStruct;
import org.streets.eis.ext.analysis.internal.SqlParser;
import org.streets.eis.ext.analysis.internal.SqlStruct;


/** 
 * 解析SQL语句为一颗树结构：SqlStruct
 * @author chq
 *
 *
 * SQL语句注意事项：
 * 1.不支持as表别名；
 */
public class SqlParserImpl implements SqlParser {
	
	private static boolean isContains(String lineText, String word) {
		Pattern pattern = Pattern.compile(word, Pattern.CASE_INSENSITIVE);
		Matcher matcher = pattern.matcher(lineText);
		return matcher.find();
	}
	
	private String getMatchedString(String regex,String text){
        Pattern pattern=Pattern.compile(regex,Pattern.CASE_INSENSITIVE);
        Matcher matcher=pattern.matcher(text);
        while(matcher.find()){
        	return matcher.group(2);//正则表达式第二组的值
        }
        return null;
	}
	
	/**
	 * 切分select子句，带函数情况:
	 * 1.字符串处理函数：eg:substr(fieldName,3,2)
	 * 2.时间函数：eg:add_months(sysdate, 6)
	 * 3.统计与数据处理：eg:count()
	 * @param select
	 * @return
	 */
	private Collection<? extends String> splitSelect(String select) {
		List<String> fieldNames = new ArrayList<String>();

		Map<String,String> replaceMap = new Hashtable<String,String>();		
		String replacedSelect = replaceBracketContent(select,replaceMap);
		String[] fragments = StringUtils.split(replacedSelect, ",");
		for(String s : fragments){		 
			s = restoreString(s,replaceMap);//恢复
			fieldNames.add(StringUtils.trim(s));
		}

		return fieldNames;
	}
		

	private Map<String,String> getJoinTableNamesMap(String from){		
		Map<String,String> tableNames = new Hashtable<String,String>();
		
		from = from.replaceAll(" left join "," join ");
		from = from.replaceAll(" right join "," join ");
		from = from.replaceAll(" inner join "," join ");
		from = from.replaceAll(" outer join "," join ");
		String[] ary = from.split("join");
		for(String s : ary){
			//去掉on后面的字符
			s = s.replaceAll("\\son\\s(.+)","").trim();
			s = s.replaceAll("\\sas\\s"," ");//去掉as
			String[] temp = s.split("([\\s]+)");//按空格分开
			if(temp.length >= 2){
				tableNames.put(temp[0], temp[1]);
			}else{
				tableNames.put(temp[0], "");
			}
		}
		return tableNames;
	}

	private Map<String,String> getTableNamesMap(String simpleFrom){
		String tmp = simpleFrom.toLowerCase();
		tmp = tmp.replaceAll("\\s+", " ");
		
		if (tmp.contains(" join ")){
			return getJoinTableNamesMap(tmp);
		}else{
			return getCommaTableNamesMap(tmp);
		}
	}
		
	private Map<String,String> getCommaTableNamesMap(String from){		
		Map<String,String> tableNames = new Hashtable<String,String>();
		String[] ary = from.split(",");
		for(String s: ary){
		   s = s.trim();
		   String[] temp = s.split("([\\s]+)");//按空格分开
		   if(temp.length >= 2){
			   tableNames.put(temp[0], temp[1]);
			}else{
			   tableNames.put(temp[0], "");
		   }
		 }
		 
		 return tableNames;
	}

 
	private void parse(String sql, SqlStruct node){
		Map<String,String> replaceMap = new Hashtable<String,String>();		
		String replacedSql = replaceBracketContent(sql,replaceMap);//这里是整个语句的带括号的部分？？？
		String simpleSelect = parseSelect(replacedSql);//在select和from之间的字符串
		String simpleFrom = parseFrom(replacedSql);//在from和where之间的字符串
		String simpleWhere = parseWhere(replacedSql);//在where后面的字符串
		String simplOrder = parseOrder(replacedSql);//在where后面的字符串
		
		
		//恢复
		String originalSelect = restoreString(simpleSelect,replaceMap);
		String originalFrom = restoreString(simpleFrom,replaceMap);		
		String originalWhere = restoreString(simpleWhere,replaceMap);
		String originalOrder = restoreString(simplOrder,replaceMap);
		
		node.select = originalSelect;
		node.where  = originalWhere;
		node.from   = originalFrom;//子句
		node.orderby = originalOrder;
		
	}
		
	private String parseFrom(String sql){
		String regex = "";
		if (isContains(sql, "\\s+where\\s+")) {
			regex = "(from)(.+)(where)";
		}
		else {
			regex = "(from)(.+)($)";
		}
	
		return getMatchedString(regex, sql);
	}
	
	private String parseSelect(String sql){
		String regex = "(select)(.+)(from)";
		return getMatchedString(regex, sql);
	}
	
	private String parseWhere(String sql) {
		String regex = "";
		if (isContains(sql, "\\s+where\\s+")) {// 包括Where，有条件
			if(isContains(sql, "\\s+order\\s+by\\s+")) {// 包括ORDER BY，有条件
				regex = "(where)(.+)(order )";// 条件在where到字符串末尾
			}else{
				regex = "(where)(.+)($)";// 条件在where到字符串末尾
			}
		}
		else {
			return null;
		}

		return  getMatchedString(regex, sql);
	}

	private String parseOrder(String sql) {
		String regex = "";
		if (isContains(sql, "\\s+order\\s+by\\s+")) {// 包括order，有条件
			regex = "(\\s+order\\s+by\\s+)(.+)($)";// 条件在order by到字符串末尾
		}
		else {
			return null;
		}

		return  getMatchedString(regex, sql);
	}

	private String replaceBracketContent(String sql, Map<String,String> m){ 
		m.clear();
		int firstLeftBlockPos = sql.indexOf("(");  
		int lastRightBlockPos = -1;  
	           
		int i=0;
		while(firstLeftBlockPos != -1){//直到没有“（”
			//get matched ')', record the substring between them  
			int leftBlockNum = 1;  
			for(int start = firstLeftBlockPos + 1; start < sql.length(); start++){//循环字符,找到匹配的)  
				if(sql.charAt(start) == ')'){  
					if(leftBlockNum == 1){  
						lastRightBlockPos = start;  
						break;  
					}else{
						leftBlockNum--;  
					}  
				}else if(sql.charAt(start) == '('){  
					leftBlockNum ++;  
				}  
			}  
		
			if(lastRightBlockPos < 0){    
				throw new RuntimeException("block mismatch");  
			}  
		
			String blockContent = sql.substring(firstLeftBlockPos+1, lastRightBlockPos);    
		 	if(blockContent.contains("select ")){//这个地方有问题????应该判断是否以select开头  
		 		//getLevelOneTables(blockContent, s);
		 		i++;
		 		String replacement = SqlStruct.SUB_QUERY_NAME_PREFIX.toLowerCase() + i;
		 		sql = sql.substring(0,firstLeftBlockPos)+" "+ replacement +" "+sql.substring(lastRightBlockPos+1);
		 		m.put(replacement, blockContent);
		 	}
		 	else{  
		 		//TODO:...修改这里以支持函数的判断
		 		i++;
		 		String replacement = SqlStruct.SUB_QUERY_NAME_PREFIX.toLowerCase() + i;
		 		//sql = sql.substring(0,firstLeftBlockPos) + sql.substring(firstLeftBlockPos+1,lastRightBlockPos) + sql.substring(lastRightBlockPos +1);
		 		sql = sql.substring(0,firstLeftBlockPos)+""+ replacement +" "+sql.substring(lastRightBlockPos+1);
		 		m.put(replacement, blockContent);
		 	}  
			
		 	firstLeftBlockPos = sql.indexOf("(");  
		}
		
	 
		return sql;
		
	}


	private String restoreString(String replacedString , Map<String,String> map){
		if (StringUtils.isEmpty(replacedString)){
			return null;
		}
	
		for(String replacement:map.keySet()){
			String value = "("+map.get(replacement)+")";
			replacedString = StringUtils.replace(replacedString,replacement,value);
		}
	
		return replacedString;
	}
	
//	private SqlType judgeSqlType(String sql){
//		if (sql.toLowerCase().trim().startsWith("select")) {
//			return SqlType.SELECT;
//		}else if(sql.trim().startsWith("update")) {
//			return SqlType.UPDATE;
//		}else if (sql.trim().startsWith("delete")){
//			return SqlType.DELETE;
//		}else{
//			return SqlType.UNKNOWN;
//		}
//	}
	
	private String parseUpdate(String sql){
		String regex = "(update)(.+)( set )";
		return getMatchedString(regex, sql);
	}
	
	private String parseDelete(String sql){
		String regex = "";
		if (isContains(sql, "\\s+where\\s+")) {
			regex = "(delete)(.+)(where)";
		}
		else {
			regex = "(delete)(.+)($)";
		}
	
		return getMatchedString(regex, sql);
	}
	
	private String parseSet(String sql){
		String regex = "";
		if (isContains(sql, "\\s+where\\s+")) {
			regex = "( set )(.+)(where)";
		}
		else {
			regex = "( set )(.+)($)";
		}
	
		return getMatchedString(regex, sql);
	}

	//只解析一层 : delete from <table_name>  where <where_clause>;
	//解析的时候，from里面放的delete后面的表字符串;	tableNamesMap里面放表名
	private void simpleDeleteParse(String sql, SqlStruct node){
		String tmp = StringUtils.replace(sql," from ","");//去掉from
		node.from = parseDelete(tmp);//在delete和where之间的字符串
		node.where = parseWhere(tmp);//在where后面的字符串

		String[] names = StringUtils.split(node.from.trim(), " ");//只支持一张表
		if (names.length >= 2 ){
			node.tableNamesMap.put(names[0].trim(),names[1].trim());
		}else if (names.length >= 1 ){
			node.tableNamesMap.put(names[0].trim(),"");
		}else{
			throw new RuntimeException("SQL:\""+ sql + "\" \n  Error: not have table name !");
		}
		
		
	}
	
	//只解析一层:update <table_name> set <column_assignment>  where <where_clause>;
	//解析的时候，from里面放的UPDATE后面的表字符串;fieldNames里面放的是 "fieldName = value"		
	private void simpleUpdateParse(String sql, SqlStruct node){
		node.select = parseSet(sql);
		node.from = parseUpdate(sql);//在delete和where之间的字符串
		node.where = parseWhere(sql);//在where后面的字符串

		String[] names = StringUtils.split(node.from.trim(), " ");//只支持一张表
		if (names.length >= 2 ){
			node.tableNamesMap.put(names[0].trim(),names[1].trim());
		}else if (names.length >= 1 ){
			node.tableNamesMap.put(names[0].trim(),"");
		}else{
			throw new RuntimeException("SQL:\""+ sql + "\" \n  Error: not have table name !");
		}
		

		node.fieldNames.addAll(splitSelect((node.select)));
		
	}


	public QueryStruct parse(String sql){
		QueryStruct q = new QueryStruct();
		//TODO:parse sql to query entity;
		return q;
	}
	
	public SqlStruct parse1(String sql){
		SqlStruct root = new SqlStruct();
		root.parent = null;
		root.sql = sql;		
		//root.sqlType = judgeSqlType(sql);
		parse(sql, root,0);
//		switch(root.sqlType){
//			case SELECT : parse(sql, root,0);break;
//			case DELETE : simpleDeleteParse(sql, root);break;
//			case UPDATE : simpleUpdateParse(sql,root);break;
//			default:throw new RuntimeException("unsupport this sql parse!");
//		}
		return root;		
	}
	
	
	public void parse(String sql, SqlStruct node,int level){


		node.level = ++ level;
		//1.分析SQL语句，取得select,from,where
		parse(sql,node);
		//2.分析From子句
		Map<String,String> fromReplacementMap = new Hashtable<String,String>();		
		node.replacedFrom  = replaceBracketContent(node.from,fromReplacementMap);//TODO:需要区别函数和子查询
		node.replacementMap = fromReplacementMap;
		//3.取得查询的字段
		node.fieldNames.addAll(splitSelect((node.select)));
		//4.取得表名和别名包括子查询的名字和别名
		node.tableNamesMap.putAll(getTableNamesMap(node.replacedFrom));//本条From子句的表名与别名,包括子查询的名字和别名
	 
		if (fromReplacementMap.size() > 0){//fromReplaceMap里面放的是:【替换名:子查询String】
			for(String tmpName : node.tableNamesMap.keySet()){//【tableName/替换名:alias】
				if (fromReplacementMap.containsKey(tmpName)){
					String subSql = fromReplacementMap.get(tmpName); 
					String alias = node.tableNamesMap.get(tmpName);//子查询的别名
					if  ( ! (StringUtils.isEmpty(alias))){//子查询别名为空
						//子查询对象
						SqlStruct childNode = new SqlStruct();
						childNode.sql = subSql;
						childNode.parent = node;
						childNode.alias = alias;
						node.fromChildren.add(childNode);
						parse(subSql,childNode,level );//解析
					}
				}
			}			
		}	
	}
}